using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Reflection;

using GURUCORE.Framework.Core;
using GURUCORE.Framework.Core.Data;
using GURUCORE.Framework.DataAccess.Provider;
using GURUCORE.Framework.DataAccess.ORMapping.CriteriaMapping;
//using GURUCORE.Framework.Core.Indexing;
//using GURUCORE.Framework.DataAccess.Indexing;

namespace GURUCORE.Framework.DataAccess.ORMapping
{
    /// <summary>
    /// this class is made for getting data in ADO.NET style (DataTable, DataSet, ...), not the DTOBase style
    /// </summary>
    public class DBHelper : IExpressionMaker
    {
        #region IExpressionMaker Members

        public string FormatStringConstant(string p_sInput)
        {
            return DataAccessContext.GetDataAccessContext().GetUnicodeForm().Replace("value", p_sInput.Replace("'", "''"));
        }

        #endregion



        /// <summary>
        /// USE for aggregrate function to get count, sum, ... Use ONLY FOR GENERATE query, do not use with developer query.
        /// </summary>
        /// <param name="p_sSqlQuery"></param>
        /// <returns></returns>
        public object ExecuteScalar(string p_sSqlQuery, params IDataParameter[] parameters)
        {
            DataAccessContext oDACtx = DataAccessContext.GetDataAccessContext();
            IDbConnection oDbConn = oDACtx.GetConnection();
            //do query
            IDbCommand oDbCmd = oDACtx.GetDbCommand(oDbConn);
            oDbCmd.CommandText = p_sSqlQuery;
            oDbCmd.CommandType = CommandType.Text;

            foreach (IDataParameter p in parameters)
            {
                oDbCmd.Parameters.Add(p);
            }

            object oRet = null;
            try
            {
                oRet = oDbCmd.ExecuteScalar();
                oDbCmd.Parameters.Clear();
            }
            catch (DbException ex)
            {
                GException.ProcessException(ex); // throw ex;
            }
            finally
            {
                oDACtx.ReturnConnection(oDbConn);
            }
                            
            return oRet;            
        }


        public object ExecuteNonQuery(string sql, params IDataParameter[] parameters)
        {
            DataAccessContext oDACtx = DataAccessContext.GetDataAccessContext();
            IDbConnection oDbConn = oDACtx.GetConnection();

            //do query
            IDbCommand oDbCmd = oDACtx.GetDbCommand(oDbConn);
            oDbCmd.CommandText = sql;
            oDbCmd.CommandType = CommandType.Text;

            foreach (IDataParameter p in parameters)
            {
                oDbCmd.Parameters.Add(p);
            }

            object oRet = null;
            try
            {
                oRet = oDbCmd.ExecuteNonQuery();
                oDbCmd.Parameters.Clear();
            }
            catch (DbException ex)
            {
                GException.ProcessException(ex); // throw ex;
            }
            finally
            {
                oDACtx.ReturnConnection(oDbConn);
            }

            return (oRet);
        }


        public DTOBase[] ExecuteQuery(string sql, Type p_returnType, params string[] p_arrColumn)
        {
            DataAccessContext oDACtx = DataAccessContext.GetDataAccessContext();
            TableMapperNonGeneric tblNonGeneric = new TableMapperNonGeneric();
            return tblNonGeneric._GetObjectsBySqlQueryAsDTO(oDACtx, p_returnType, sql, p_arrColumn);            
        }




        /// <summary>
        /// Execute a SqlCommand (that returns a resultset and takes parameters) against the database. 
        /// </summary>
        /// <param name="sql">A valid sql statement, or a store procedure call with explicit parameters. Eg: SP_TEST 20,'test string param'</param>
        /// <param name="parameters">An array of DbParamters used to execute the command</param>
        /// <returns>A dataset containing the resultset generated by the command</returns>
        public DataSet GetDataSet(string sql, params IDataParameter[] parameters)
        {
            DataAccessContext oDACtx = DataAccessContext.GetDataAccessContext();
            IDbConnection oDbConn = oDACtx.GetConnection();
            
                IDbCommand oDbCmd = oDACtx.GetDbCommand(oDbConn);
                oDbCmd.CommandType = CommandType.Text;
                oDbCmd.CommandText = sql;
                foreach (IDataParameter p in parameters)
                {
                    oDbCmd.Parameters.Add(p);
                }

                IDbDataAdapter adapter = oDACtx.GetDbDataAdapter();
                adapter.SelectCommand = oDbCmd;
                DataSet ds = new DataSet();
                try
                {
                    adapter.Fill(ds);
                    oDbCmd.Parameters.Clear();
                }
                catch (DbException ex)
                {
                    GException.ProcessException(ex); // throw ex;
                }
                finally
                {
                    oDACtx.ReturnConnection(oDbConn);
                }

                return (ds);
            
        }

        /// <summary>
        /// WRAPPER: Execute a SqlCommand (that returns a resultset and takes parameters) against the database. 
        /// </summary>
        /// <param name="sql">A valid sql statement</param>
        /// <param name="parameters">An array of DbParamters used to execute the command</param>
        /// <returns>A first DataTable of the dataset containing the resultset generated by the command</returns>
        public DataTable GetDataTable(string sql, params IDataParameter[] parameters)
        {
            return (GetDataSet(sql, parameters).Tables[0]);  
        }
        
        /// <summary>
        /// WRAPPER: GetDataSet()
        /// </summary>
        /// <param name="spcommand"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySPText(string spcommand)
        {
            return GetDataSet(spcommand);            
        }





        /// <summary>
        /// GetDataSet with CommandType = StoreProcedure
        /// </summary>
        /// <param name="spcommand"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySP(string spcommand, params IDataParameter[] parameters)
        {
            DataAccessContext oDACtx = DataAccessContext.GetDataAccessContext();
            IDbConnection oDbConn = oDACtx.GetConnection();
            
            IDbCommand oDbCmd = oDACtx.GetDbCommand(oDbConn);
            oDbCmd.CommandType = CommandType.StoredProcedure;
            oDbCmd.CommandText = spcommand;
            foreach (IDataParameter p in parameters)
            {
                oDbCmd.Parameters.Add(p);
            }
            IDbDataAdapter adapter = oDACtx.GetDbDataAdapter();
            adapter.SelectCommand = oDbCmd;

            DataSet ds = new DataSet();
            try
            {
                adapter.Fill(ds);
                oDbCmd.Parameters.Clear();
            }
            catch (DbException ex)
            {
                GException.ProcessException(ex); // throw ex;
            }
            finally
            {
                oDACtx.ReturnConnection(oDbConn);
            }

            return (ds);            
        }









        #region UTIL STATIC DATA FUNCTION
        
        

        /// <summary>
        /// Convert DTOCollecton to DataTable
        /// </summary>
        /// <param name="p_tDTOType"></param>
        /// <param name="p_arrData"></param>
        /// <returns></returns>
        public static DataTable ConvertToDataTable(Type p_tDTOType, DTOBase[] p_arrData)
        {
            ColumnInfoReader oReader = new ColumnInfoReader(p_tDTOType);
            DataTable dtbResult = new DataTable();

            object[] arrAttributes = p_tDTOType.GetCustomAttributes(typeof(PersistenceClassAttribute), false);

            while (oReader.Read())
            {
                string sName = oReader.FieldName;
                PropertyInfo oProp = oReader.PropertyInfo;

                DataColumn col = new DataColumn(sName, oProp.DeclaringType);

                dtbResult.Columns.Add(col);

                if (arrAttributes.Length > 0)
                {
                    PersistenceClassAttribute oAttr = (PersistenceClassAttribute)arrAttributes[0];
                    if (sName == oAttr.PKField)
                    {
                        dtbResult.PrimaryKey = new DataColumn[] { col };
                    }
                }
            }


            foreach (DTOBase dtoObject in p_arrData)
            {
                oReader.First();
                DataRow drObject = dtbResult.NewRow();
                while (oReader.Read())
                {
                    object oValue = oReader.PropertyInfo.GetValue(dtoObject, null);
                    drObject[oReader.FieldName] = oValue;
                }
                dtbResult.Rows.Add(drObject);
            }

            return dtbResult;
        }

        public static GMetaDTO ConvertToGMetaDTO(DataRow p_row)
        {
            GMetaDTO dtoRet = new GMetaDTO();
            dtoRet.TableName = p_row.Table.TableName;

            string sPrimaryKeyField = string.Empty;
            foreach(DataColumn col in p_row.Table.PrimaryKey)
            {
                sPrimaryKeyField += col.ColumnName + ",";
            }
            sPrimaryKeyField.TrimEnd(',');
            dtoRet.KeyField = sPrimaryKeyField;

            foreach (DataColumn col in p_row.Table.Columns)
            {
                dtoRet[col.ColumnName] = p_row[col];
            }

            return dtoRet;
        }


        /// <summary>
        /// Thachnn: 14/Oct/2005
        /// Merge multi DataTables (have same schema) from a dataset into only one DataTable
        /// </summary>
        /// <param name="pdst">DataSet contain table(s) to merge</param>
        /// <returns>result DataTable if OK, empty DataTable if fail or error</returns>
        public static DataTable MergeManyDataTablesIntoOne(DataSet pdst)
        {
            DataTable dtbRet = new DataTable();
            try
            {
                if (pdst.Tables.Count > 0)
                {
                    dtbRet = pdst.Tables[0].Clone();

                    foreach (DataTable table in pdst.Tables)
                    {
                        /// Don't change this method to Rows.AddRange please. Thachnn.
                        foreach (DataRow drow in table.Rows)
                        {
                            dtbRet.ImportRow(drow);
                        }
                    }
                }
            }
            catch //(Exception ex)
            {
                // throw ex;
            }
            return dtbRet;
        }

        /// <summary>
        /// Thachnn : 15/Oct/2005
        /// Browse the DataTable, get distinct all value of column with provided named.
        /// </summary>
        /// <param name="pdtb">DataTable to collect values</param>
        /// <param name="pstrColumnName">COlumn Name in pdtb DataTable to collect values from</param>
        /// <returns>ArrayList of object, collect from pdtb's column named pstrColumnName. Empty ArrayList if error or not found any row in pdtb.</returns>
        public static List<T> GetDistinctColumnValuesFromTable<T>(DataTable pdtb, string pstrColumnName)
        {
            List<T> arrRet = new List<T>();

            foreach (DataRow drow in pdtb.Rows)
            {
                T objGet = (T)drow[pstrColumnName];
                if (!arrRet.Contains(objGet))
                {
                    arrRet.Add(objGet);
                }
            }

            return arrRet;
        }


        #endregion




    }
}
